import traceback
import pymysql
import baostock as bs
import pandas as pd
from decimal import Decimal
import math
import datetime

from util.daylink import daylink


class  dataInput:

    def is_number(self,s):
        try:
            float(s)
            return True
        except ValueError:
            pass
        return False

    # 每日数据更新
    def sql(self):
        try:
            print('数据写入')
            db = pymysql.connect(host='101.35.200.120', port=3306, user='root', passwd='ljf123456', db='gp', charset='utf8')
            # 使用cursor()方法获取操作游标
            cursor = db.cursor()
            d=daylink().get_day_of_day().strftime('%Y-%m-%d')#datetime.datetime.now().strftime('%Y-%m-%d')
            lg = bs.login(user_id='anonymous', password='123456')
            rss = bs.query_all_stock(day=d )
            # rs = bs.query_all_stock(day='2022-3-27')
            # rs = bs.query_hs300_stocks()
            # bs.query_hs300_stocks()  # query_hs300_stocks()  query_zz500_stocks()

            while (rss.error_code == '0') & rss.next():

                    try:

                        code = rss.get_row_data()[0]
                        #print(code)
                        name =  rss.get_row_data()[2]
                        # print(code)
                        if ("bj" in code) == False:
                            scode = code.split('.', 1)
                            scode=scode[1]
                            sql = """SELECT table_name FROM information_schema.TABLES WHERE table_name ='record_data_%s'""" % (scode)
                            # 执行SQL语句
                            cursor.execute(sql)
                            # 获取所有记录列表
                            results = cursor.fetchall()
                            if (len(results) == 0):
                               sql="""CREATE TABLE `record_data_%s`  (`id` bigint(12) NOT NULL AUTO_INCREMENT,`time` date NULL DEFAULT NULL,`time_val` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`code` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`name` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`opening_price` double(6, 2) NULL DEFAULT NULL,`max_price` double(6, 2) NULL DEFAULT NULL,`min_price` double(6, 2) NULL DEFAULT NULL,`close_price` double(6, 2) NULL DEFAULT NULL,`today_range` double(10, 2) NULL DEFAULT NULL,`today_range_price` double(10, 2) NULL DEFAULT NULL,`turnover` bigint(25) NULL DEFAULT NULL,`turnover_amount` bigint(25) NULL DEFAULT NULL,`amplitude` double(20, 2) NULL DEFAULT NULL,`turnover_rate` double(20, 2) NULL DEFAULT NULL,`region_code` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`preclose` double(20, 2) NULL DEFAULT NULL,`tradestatus` int(6) NULL DEFAULT NULL,`pe_ttm` double(6, 2) NULL DEFAULT NULL,`pb_mrq` double(6, 2) NULL DEFAULT NULL,`ps_ttm` double(6, 2) NULL DEFAULT NULL,`pcf_ncf_ttm` double(6, 2) NULL DEFAULT NULL,`is_st` int(6) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 7573 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;"""% (scode)
                               cursor.execute(sql)
                               db.commit()
                               # 获取最后日期 ALTER TABLE trade_history ADD INDEX index_name ( time )
                            sSql = """select id, time_val from record_data_%s  where  code = '%s'  order by time desc limit 1 """ % (scode, scode)
                            cursor.execute(sSql)
                            sResults = cursor.fetchall()
                            if (len(sResults) > 0):
                                  for row in sResults:
                                      in_date = row[1]
                                      if (in_date == None):
                                          data = '1990-01-01'
                                      else:
                                          dt = datetime.datetime.strptime(in_date, "%Y-%m-%d")
                                          data = (dt + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
                            else:
                                  data = '1990-01-01'

                            if (datetime.datetime.strptime(data, "%Y-%m-%d") <= datetime.datetime.now()):
                                  rs = bs.query_history_k_data_plus(code,
                                                                    "date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,peTTM,pbMRQ,psTTM,pcfNcfTTM,isST",
                                                                    start_date=data, end_date='%s-%s-%s' % (
                                          datetime.datetime.now().year, datetime.datetime.now().month,
                                          datetime.datetime.now().day),
                                                                    frequency="d",
                                                                    adjustflag="3")

                                  while (rs.error_code == '0') & rs.next():
                                      li = rs.get_row_data()
                                      openingPrice = li[2];
                                      openingPrice = openingPrice if self.is_number(openingPrice) else 0
                                      # is_number(openingPrice)
                                      # openingPrice=is_number(openingPrice)==True?openingPrice:0;
                                      maxPrice = li[3];
                                      maxPrice = maxPrice if self.is_number(maxPrice) else 0
                                      minPrice = li[4];
                                      minPrice = minPrice if self.is_number(minPrice) else 0
                                      closePrice = li[5];
                                      closePrice = closePrice if self.is_number(closePrice) else 0
                                      preclose = li[6];
                                      preclose = preclose if self.is_number(preclose) else 0
                                      turnover = li[7];
                                      turnover = turnover if self.is_number(turnover) else 0
                                      turnover = float(turnover)
                                      turnover = math.trunc(turnover)
                                      turnoverAmount = li[8];
                                      turnoverAmount = turnoverAmount if self.is_number(turnoverAmount) else 0
                                      turnoverAmount = float(turnoverAmount)
                                      turnoverAmount = math.trunc(turnoverAmount)
                                      turnoverRate = li[10];
                                      turnoverRate = turnoverRate if self.is_number(turnoverRate) else 0
                                      tradestatus = li[11];
                                      tradestatus = tradestatus if self.is_number(tradestatus) else 0
                                      todayRange = li[12];
                                      todayRange = todayRange if self.is_number(todayRange) else 0
                                      peTtm = li[13];
                                      peTtm = peTtm if self.is_number(peTtm) else 0
                                      pbMrq = li[14];
                                      pbMrq = pbMrq if self.is_number(pbMrq) else 0
                                      psTtm = li[15];
                                      psTtm = psTtm if self.is_number(psTtm) else 0
                                      pcfNcfTtm = li[16];
                                      pcfNcfTtm = pcfNcfTtm if self.is_number(pcfNcfTtm) else 0
                                      isSt = li[17];
                                      isSt = isSt if self.is_number(isSt) else 0
                                      sql1 = """INSERT INTO record_data_%s (time,time_val, region_code, opening_price, max_price, min_price, close_price, preclose, turnover,
                                                                       turnover_amount, turnover_rate, tradestatus, today_range, pe_ttm, pb_mrq, ps_ttm, pcf_ncf_ttm,
                                                                        is_st,name,code) VALUES ('%s','%s', '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') """ \
                                             % (scode, li[0], li[0], li[1], openingPrice, maxPrice, minPrice, closePrice,
                                                preclose,
                                                turnover, turnoverAmount, turnoverRate, tradestatus, todayRange, peTtm,
                                                pbMrq, psTtm,
                                                pcfNcfTtm, isSt, name, scode)


                                      cursor.execute(sql1)
                                      # 执行sql语句
                                      db.commit()
                    except Exception as e:
                        print(e)
                        # f = open('D:/except.txt', 'a')
                        # f.write('\n' + sql1)
                        # 发生错误时回滚
                        db.rollback()

        except Exception as e:
                #traceback.print_exc()
                print("Error: unable to fecth data")
        finally:
                try:
                    # 关闭数据库连接
                    db.close()
                    bs.logout()
                except Exception as e:
                    traceback.print_exc()
                    print("Error: unable to fecth data")

    def fqyz(self):
        try:
            print('复权数据写入')
            # 登陆系统
            lg = bs.login()
            # 打开数据库连接
            db = pymysql.connect(host='101.35.200.120', port=3306, user='root', passwd='ljf123456', db='gp', charset='utf8')
            # 使用cursor()方法获取操作游标
            cursor = db.cursor()
            # SQL 查询语句
            sql = "select * from shares "

            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
            for row in results:
                    id = row[0]
                    code = row[1]
                    name = row[2]
                    regionCode = row[5]
                    # 查询2015至2017年复权因子
                    rs_list = []
                    rs_factor = bs.query_adjust_factor(code=regionCode, start_date="1990-01-01", end_date='%s-%s-%s' % (
                        datetime.datetime.now().year, datetime.datetime.now().month, datetime.datetime.now().day))
                    while (rs_factor.error_code == '0') & rs_factor.next():
                        li = rs_factor.get_row_data()
                        dividOperateDate = li[1]
                        foreAdjustFactor = li[2]
                        backAdjustFactor = li[3]
                        sql1 = """INSERT INTO adjust_factor(code, region_code, name, divid_operate_date, operate_date, fore_adjust_factor, back_adjust_factor) VALUES ('%s', '%s','%s','%s','%s','%s','%s') """ \
                               % (code, regionCode, name, dividOperateDate, dividOperateDate, foreAdjustFactor,
                                  backAdjustFactor)
                        try:
                            selectSql = """select * from adjust_factor  WHERE  code = '%s'  AND operate_date = '%s' """ \
                                        % (code, dividOperateDate)
                            # print(selectSql)
                            cursor.execute(selectSql)
                            selectResults = cursor.fetchall()
                            if (len(selectResults) == 0):
                                cursor.execute(sql1)
                                # 执行sql语句
                            else:
                                id = row[0]
                                sql1 = """UPDATE adjust_factor SET   fore_adjust_factor = %s  and back_adjust_factor = %s  where  id = %s """ % (
                                foreAdjustFactor, backAdjustFactor, id)
                                cursor.execute(sql1)
                            db.commit()
                        except Exception as e:
                            # 发生错误时回滚
                            db.rollback()
        except Exception as e:
            traceback.print_exc()
            print("Error: unable to fecth data")

        finally:
            try:
                # 关闭数据库连接
                db.close()
                bs.logout()
            except Exception as e:
                traceback.print_exc()
                print("Error: unable to fecth data")



